﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;

namespace WebApplication1
{
    public partial class usracc : System.Web.UI.Page
    {
        string no;
        double dy;
        int fin,totfine=0;
        DateTime exp;
        SqlConnection cnn = new SqlConnection(WebConfigurationManager.AppSettings["connect"]);
        protected void Page_Load(object sender, EventArgs e)
        {
            no = Convert.ToString(Session["no"]);
            Label3.Text = no;
            Label3.Visible = true;
            cnn.Open();

            //select all cd details for user
            SqlCommand cmd = new SqlCommand("select distinct c.name,c.lang,c.publish,c.year,d.status,d.issdate,d.expire,d.fine from account a,cdinfo c,cdissue d where d.status<>'Returned' and d.cdno=c.cdno and d.custno=a.accno and a.accno='" + no + "'", cnn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ds.Clear();
            da.Fill(ds, "account");
            if (ds.Tables["account"].Rows.Count <= 0)
            {
                errlabel.Visible = true;
                errlabel.Text = "No results";
                GridView1.Visible = false;
            }
            else
            {
                //calculate fine and display
                SqlCommand cmd2 = new SqlCommand("select * from cdissue c where c.custno='" + no + "' and cdno=(select cdno from cdissue where status='Issued') ", cnn);
                SqlDataReader dr0 = cmd2.ExecuteReader();
                if (dr0.HasRows == true)
                {
                    while (dr0.Read())
                    {
                        fin = 0;
                        exp = Convert.ToDateTime(dr0["expire"]);
                        DateTime tdy = DateTime.Today;
                        int compare_val = tdy.CompareTo(exp);
                        if (compare_val > 0)
                        {
                            TimeSpan nody = tdy.Subtract(exp);
                            dy = nody.TotalDays;
                            fin = (int)dy * 5;
                            SqlCommand cmd3 = new SqlCommand("update cdissue d set d.fine='" + fin + "' where d.cdno=c.cdno", cnn);
                            cmd3.ExecuteNonQuery();
                            totfine = totfine + fin;
                        }
                    }
                    //calculate total fine to be paid
                    SqlCommand cmd4 = new SqlCommand("update account set fine='" + totfine + "' where accno='" + no + "'", cnn);
                    cmd4.ExecuteNonQuery();
                }
                dr0.Close();

                errlabel.Visible = false;
                GridView1.Visible = true;
                GridView1.DataSource = ds;
                GridView1.DataBind();

                //display the no of cds taken anf alfo the fine
                SqlCommand cmd1 = new SqlCommand("select a.no_cd,a.fine from account a where a.accno='" + no + "'", cnn);
                SqlDataReader dr = cmd1.ExecuteReader();
                while (dr.Read())
                {
                    Label2.Visible = true;
                    Label4.Visible = true;
                    cdcount.Visible = true;
                    fine.Visible = true;
                    cdcount.Text = Convert.ToString(dr["no_cd"]);
                    fine.Text = Convert.ToString(dr["fine"]);
                }
            }
            cnn.Close();
        }
    }
}